package com.starsky.common.util.easypoi;

import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.csv.entity.CsvImportParams;
import cn.afterturn.easypoi.csv.export.CsvExportService;
import cn.afterturn.easypoi.csv.imports.CsvImportService;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import com.starsky.common.util.DateUtils;
import com.starsky.common.util.reflect.ReflectUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * Excel工具类
 * easypoi官网：http://easypoi.mydoc.io/
 */
@Slf4j
public class ExcelUtils {
    public static int USE_SXSSF_LIMIT = 100000;

    private static final Integer ENDROW = 3000;

    private ExcelUtils() {

    }


    /**
     * 创建workbook
     *
     * @return
     */
    public static Workbook createWorkbook(InputStream in) {
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(in);
//            createWorkbook(in,ExcelType.XSSF);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 创建workbook
     *
     * @param file
     * @param type
     * @return
     */
    public static Workbook createWorkbook(MultipartFile file, ExcelType type) {
        Workbook workbook = null;
        try {
            workbook = createWorkbook(file.getInputStream(), type);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 创建workbook
     *
     * @param in
     * @return
     */
    public static Workbook createWorkbook(InputStream in, ExcelType type) {
        Workbook wb = null;
        try {
            if (ExcelType.XSSF.equals(type)) {
                return new XSSFWorkbook(in);
            } else {
                return new HSSFWorkbook(in);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 创建workbook
     *
     * @param path
     * @return
     */
    public static Workbook createWorkbook(String path, ExcelType type) {
        path = getAbsoluteFile(path);
        return createWorkbook(new File(path), type);
    }

    /**
     * 创建workbook
     *
     * @param file
     * @return
     */
    public static Workbook createWorkbook(File file, ExcelType type) {
        Workbook wb = null;
        try {
            wb = createWorkbook(new FileInputStream(file), type);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 创建workbook
     *
     * @return
     */
    public static Workbook createWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else if (ExcelType.XSSF.equals(type)) {
            return new XSSFWorkbook();
        } else {
            return (Workbook) (size < USE_SXSSF_LIMIT ? new XSSFWorkbook() : new SXSSFWorkbook());
        }
    }


    /**
     * 创建导入参数
     * 导入Excel转换为List<T>
     *
     * @param titleRows  标头行，表示从第几行开始，默认从0开始
     * @param headerRows 标头列，表示从第几列开始，默认从1开始
     */
    public static ImportParams getImportParams(int titleRows, int headerRows) {
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return params;
    }

    /**
     * 导入多个sheet页
     *
     * @param in
     * @param clazz
     * @param params
     * @return
     */
    public static Map<String, List<T>> importMutilSheetMap(InputStream in, Class<T> clazz, ImportParams params) {

        Map<String, List<T>> map = new HashMap();
        Workbook workbook = null;
        try {
            workbook = createWorkbook(in, ExcelType.XSSF);
            int sheetNum = workbook.getNumberOfSheets();

            for (int i = 0; i < sheetNum; i++) {
                params.setStartSheetIndex(i);
                Sheet sheet = workbook.getSheetAt(i);

                List<T> objects = ExcelUtils.importExcel(in, clazz, params);
                if (objects != null && objects.size() > 0) {
                    map.put(sheet.getSheetName(), objects);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(in);
            IOUtils.closeQuietly(workbook);
        }
        return map;
    }

    /**
     * Excel导入单个sheet数据
     * 导入Excel转换为List<T>
     *
     * @param filePath   文件名
     * @param titleRows  标头行，表示从第几行开始，默认从0开始
     * @param headerRows 标头行，表示从第几列开始，默认从1开始
     * @param clazz      对象Class
     */
    public static <T> List<T> importExcel(String filePath, int titleRows, int headerRows, Class<T> clazz) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), clazz, params);
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * Excel导入单个sheet数据
     * 导入Excel转换为List<T>
     *
     * @param file       http上传的文件
     * @param titleRows  标头行，表示从第几行开始，默认从0开始
     * @param headerRows 标头行，表示从第几列开始，默认从1开始
     * @param clazz      对象Class
     */
    public static <T> List<T> importExcel(MultipartFile file, int titleRows, int headerRows, Class<T> clazz) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * Excel 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param file
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            List<T> list = ExcelImportUtil.importExcel(file, pojoClass, params);
            return list;
        } catch (Exception e) {
            throw new ExcelImportException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass,
                                          ImportParams params) throws Exception {
//        return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, false).getList();
        return ExcelImportUtil.importExcel(inputstream, pojoClass, params);
    }


    /**
     * 依然建议上面的写法,但不建议使用校验,可以加入下面参数加快时间,但是因为是一次返回可能量还是有点大,不支持一对多
     * <p>
     * Excel 通过SAX解析方法,适合大数据导入,不支持图片
     * 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params:     量稍微有点大10W内, 增加concurrentTask :是否并行计算
     * @param handler
     */
    public static <T> List<T> importExcelBySax(InputStream inputstream, Class<?> pojoClass,
                                               ImportParams params, IReadHandler handler) {
        List<T> list = null;
        try {
            list = new SaxReadExcel().readExcel(inputstream, pojoClass, params, handler);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 创建导入参数
     * 导入Excel转换为List<T>
     *
     * @param titleRows  标头行，表示从第几行开始，默认从0开始
     * @param headerRows 标头列，表示从第几列开始，默认从1开始
     */
    public static CsvImportParams getCsvImportParams(int titleRows, int headerRows) {
        CsvImportParams params = new CsvImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return params;
    }

    /**
     * 更加大量的数据,百万级以上
     * 使用csv把,这个靠谱的多,百万级读取数据也相当的快,内存也不高
     * Csv 导入流适合大数据导入
     * 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importCsv(InputStream inputstream, Class<?> pojoClass,
                                        CsvImportParams params) {
        return new CsvImportService().readExcel(inputstream, pojoClass, params, null);
    }

    /**
     * Csv 导入流适合大数据导入
     * 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importCsv(InputStream inputstream, Class<?> pojoClass,
                                        CsvImportParams params, IReadHandler readHandler) {
        List<T> list = new CsvImportService().readExcel(inputstream, pojoClass, params, readHandler);
        return list;
    }

    /**
     * @param params    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static void exportCsv(CsvExportParams params, Class<?> pojoClass,
                                 Collection<?> dataSet, OutputStream outputStream) {
        new CsvExportService().createCsv(outputStream, params, pojoClass, dataSet);
    }

    /**
     * 根据Map创建对应的Excel
     *
     * @param params     表格标题属性
     * @param entityList Map对象列表
     * @param dataSet    Excel对象数据List
     */
    public static void exportCsv(CsvExportParams params, List<ExcelExportEntity> entityList,
                                 Collection<?> dataSet, OutputStream outputStream) {
        new CsvExportService().createCsvOfList(outputStream, params, entityList, dataSet);
    }


    /**
     * Excel导出单个sheet
     *
     * @param entity    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
        Workbook workbook = ExcelExportUtil.exportExcel(entity, pojoClass, dataSet);
        return workbook;
    }


    /**
     * Excel导出单个sheet
     *
     * @param response response
     * @param fileName 文件名
     * @param workbook 创建导出excel
     */
    public static void writeResponse(HttpServletResponse response, Workbook workbook, String fileName) {

        try {
            Sheet sheet1 = workbook.getSheetAt(0);
            sheet1.setDefaultColumnWidth(50 * 256);
            sheet1.setDefaultRowHeight((short) (2 * 256));
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Excel导出单个sheet
     *
     * @param out:输出流
     * @param workbook 创建导出excel
     */
    public static void writeResponse(OutputStream out, Workbook workbook) {

        try {
            Sheet sheet1 = workbook.getSheetAt(0);
            sheet1.setDefaultColumnWidth(50 * 256);
            sheet1.setDefaultRowHeight((short) (2 * 256));
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * Excel导出单个sheet
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, ExportParams exportParams,
                                   String fileName, Collection<?> list,
                                   Class<?> pojoClass) {
        if (StringUtils.isBlank(fileName)) {
            // 当前日期
            fileName = DateUtils.format(new Date(), null);
        }

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        ExcelUtils.writeResponse(response, workbook, fileName);
    }

    /**
     * Excel导出单个sheet
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String title,
                                   String fileName, Collection<?> list,
                                   Class<?> pojoClass) {
        if (StringUtils.isBlank(fileName)) {
            // 当前日期
            fileName = DateUtils.format(new Date(), null);
        }
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        ExcelUtils.writeResponse(response, workbook, fileName);
    }

    /**
     * Excel导出单个sheet
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) {
        if (StringUtils.isBlank(fileName)) {
            // 当前日期
            fileName = DateUtils.format(new Date(), null);
        }

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, list);
        ExcelUtils.writeResponse(response, workbook, fileName);
    }


    /**
     * Excel导出sheet，先sourceList转换成List<targetClass>，再导出
     *
     * @param response  response
     * @param fileName  文件名
     * @param dataList  原数据List
     * @param pojoClass 目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> dataList,
                                           Class<?> pojoClass) throws Exception {
        List<Object> targetList = new ArrayList<>(dataList.size());
        for (Object source : dataList) {
            Object target = pojoClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }
        ExcelUtils.exportExcel(response, fileName, targetList, pojoClass);
    }


    /**
     * 创建sheet需要的信息
     *
     * @param title：sheet       标题
     * @param sheetName：sheet名称
     * @return
     */
    public static ExportParams getExportParams(String title, String sheetName) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        return exportParams;
    }

    /**
     * 创建sheet需要的信息
     *
     * @param exportParams：sheet相关参数
     * @param entity：导出sheet对应class类
     * @param data：导出数据
     * @return
     */
    public static Map<String, Object> getSheetMap(ExportParams exportParams, Object entity, List data) {
        Map<String, Object> map = new HashMap<>();
        map.put("title", exportParams);
        map.put("entity", entity);
        map.put("data", data);
        return map;
    }

    /**
     * Execl导出多个sheet
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param dataList list: 多个Map ,
     *                 Map<String, Object> map = new HashMap<>();
     *                 map.put("title", ExportParams);
     *                 map.put("entity", 到处实体类class);
     *                 map.put("data", list集合数据);
     *                 list.add(map);
     *                 每个map中必须保护key为title，即对应表格Title；
     *                 key为 entity，即对应表格对应实体
     *                 key为data，即Collection数据
     * @param filename 文件名
     */
    public static void exportExcel(List<Map<String, Object>> dataList, String filename) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            FileOutputStream fos = new FileOutputStream(filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Execl导出多个sheet
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param dataList     list: 多个Map ,
     *                     Map<String, Object> map = new HashMap<>();
     *                     map.put("title", ExportParams);
     *                     map.put("entity", 到处实体类class);
     *                     map.put("data", list集合数据);
     *                     list.add(map);
     *                     每个map中必须保护key为title，即对应表格Title；
     *                     key为 entity，即对应表格对应实体
     *                     key为data，即Collection数据
     * @param outputStream 输出文件流
     */
    public static void exportExcel(List<Map<String, Object>> dataList, OutputStream outputStream) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * Execl导出多个sheet
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param dataList list: 多个Map ,
     *                 Map<String, Object> map = new HashMap<>();
     *                 map.put("title", ExportParams);
     *                 map.put("entity", 到处实体类class);
     *                 map.put("data", list集合数据);
     *                 list.add(map);
     *                 每个map中必须保护key为title，即对应表格Title；
     *                 key为 entity，即对应表格对应实体
     *                 key为data，即Collection数据
     * @param filename 文件名
     */
    public static void exportExcel(HttpServletResponse response, List<Map<String, Object>> dataList, String filename) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            ExcelUtils.writeResponse(response, workbook, filename);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出excel
     *
     * @param pojoClass
     * @param dataSet
     * @param path
     * @param filename
     */
    public static void exportExcel(Class<?> pojoClass, Collection<?> dataSet, String path, String filename) {

        try {
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, dataSet);
            FileOutputStream fos = new FileOutputStream(path + File.separator + filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel
     *
     * @param pojoClass
     * @param dataSet
     * @param path
     * @param filename
     */
    public static void exportExcel(Class<?> pojoClass, Collection<?> dataSet, ExportParams exportParams, String path, String filename) {

        try {
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, dataSet);
            FileOutputStream fos = new FileOutputStream(path + File.separator + filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param list     list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data
     *                 *             Collection 数据
     * @param path     路径
     * @param filename 　文件名
     */
    public static void exportExcel(List<Map<String, Object>> list, String path, String filename) {
        try {
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            FileOutputStream fos = new FileOutputStream(path + File.separator + filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过模板导出
     *
     * @param templateUrl：模板文件
     * @param fileName：文件名
     * @param map：模板文件数据，key-value对应；可以设置多个key-value
     */
    public static void exportTemplate(HttpServletResponse response, String templateUrl, String fileName, Map<String, Object> map) {
        TemplateExportParams temp = new TemplateExportParams(templateUrl);
        Workbook workbook = ExcelExportUtil.exportExcel(temp, map);
        ExcelUtils.writeResponse(response, workbook, fileName);
    }

    /**
     * 通过模板导出
     *
     * @param out：输入流程
     * @param templateUrl：模板文件
     * @param map：模板文件数据，key-value对应；可以设置多个key-value
     */
    public static void exportTemplate(OutputStream out, String templateUrl, Map<String, Object> map) {
        TemplateExportParams temp = new TemplateExportParams(templateUrl);
        Workbook workbook = ExcelExportUtil.exportExcel(temp, map);
        ExcelUtils.writeResponse(out, workbook);
    }


    /**
     * 获取下载路径
     *
     * @param filePath 文件全路径
     */
    public static String getAbsoluteFile(String filePath, String fileName) {
        File desc = new File(filePath + File.separator + fileName);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return filePath;
    }

    /**
     * 获取下载路径
     *
     * @param filePath 文件全路径
     */
    public static String getAbsoluteFile(String filePath) {
        File desc = new File(filePath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return filePath;
    }

    /**
     * 单元格添加下拉菜单(不限制菜单可选项个数)<br/>
     * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]<br/>
     * [待添加下拉菜单的单元格 -> 以下简称：目标单元格]
     *
     * @param @param workbook
     * @param @param tarSheet 目标单元格所在的sheet
     * @param @param menuItems 下拉菜单可选项数组
     * @param @param firstRow 第一个目标单元格所在的行号(从0开始)
     * @param @param lastRow 最后一个目标单元格所在的行(从0开始)
     * @param @param column 待添加下拉菜单的单元格所在的列(从0开始)
     */
    public static void addDropDownList(XSSFWorkbook workbook, XSSFSheet tarSheet,
                                       String[] menuItems, int firstRow, int lastRow, int column) throws Exception {
        if (null == workbook) {
            throw new Exception("workbook为null");
        }
        if (null == tarSheet) {
            throw new Exception("sheet为null");
        }

        //excel中的"名称"，用于标记隐藏sheet中的用作菜单下拉项的所有单元格
        final String replaceStr = UUID.randomUUID().toString().replace("-", "");
        String formulaId = "form" + replaceStr;
        //必须以字母开头，最长为31位
        String hiddenSheetName = "a" + replaceStr.substring(1, 31);
        //用于存储 下拉菜单数据
        XSSFSheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        //存储下拉菜单项的sheet页不显示
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

        //隐藏sheet中添加菜单数据
        for (int i = 0; i < menuItems.length; i++) {
            XSSFRow row = hiddenSheet.createRow(i);
            //隐藏表的数据列必须和添加下拉菜单的列序号相同，否则不能显示下拉菜单
            XSSFCell cell = row.createCell(column);
            cell.setCellValue(menuItems[i]);
        }

        //2.创建"名称"标签，用于链接
        String formulaText = hiddenSheetName + "!A$1:A$" + menuItems.length;
        XSSFName namedCell = workbook.createName();
        namedCell.setNameName(formulaId);
        namedCell.setRefersToFormula(formulaText);

        //3.添加下拉框序列选择数据有效验证
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(tarSheet);
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaId);

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);
        //将单元格与"名称"建立关联
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        tarSheet.addValidationData(validation);
    }


/**************************生成excel下拉列表及多级级联下拉列表******************************************/

    /**
     * 单元格添加下拉菜单(不限制菜单可选项个数)<br/>
     * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]<br/>
     * [待添加下拉菜单的单元格 -> 以下简称：目标单元格]
     *
     * @param @param workbook
     * @param @param tarSheet 目标单元格所在的sheet
     * @param @param menuItems 下拉菜单可选项数组
     * @param @param firstRow 第一个目标单元格所在的行号(从0开始)
     * @param @param lastRow 最后一个目标单元格所在的行(从0开始)
     * @param @param column 待添加下拉菜单的单元格所在的列(从0开始)
     */
    public static void addDropDownList(XSSFWorkbook workbook, XSSFSheet tarSheet,
                                       List<String> menuItems, int firstRow, int lastRow, int column) {
        if (null == workbook) {
            log.info("workbook is null");
            return;
        }
        if (null == tarSheet) {
            log.info("sheet is null");
            return;
        }

        //excel中的"名称"，用于标记隐藏sheet中的用作菜单下拉项的所有单元格
        final String replaceStr = UUID.randomUUID().toString().replace("-", "");
        String formulaId = "form" + replaceStr;
        //必须以字母开头，最长为31位
        String hiddenSheetName = "a" + replaceStr.substring(1, 31);

        //1.创建隐藏sheet,并添加下拉框数据
        ExcelUtils.createHiddenSheetData(workbook, hiddenSheetName, menuItems, column);
        //2. 创建"名称"标签，用于链接
        String formulaText = hiddenSheetName + "!A$1:A$" + menuItems.size();
        ExcelUtils.crateNameTarget(workbook, formulaId, formulaText);
        //3.添加下拉框序列选择数据有效验证
        ExcelUtils.addValidationData(tarSheet, formulaId, firstRow, lastRow, column, column);
    }

    /**
     * 创建隐藏sheet,并添加下拉框数据
     *
     * @param workbook
     * @param hiddenSheetName
     * @param menuItems
     * @param column
     */
    public static void createHiddenSheetData(Workbook workbook, String hiddenSheetName, List<String> menuItems, int column) {
        //创建隐藏sheet,用于存储下拉菜单数据
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        //存储下拉菜单项的sheet页不显示
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
        //隐藏sheet中添加菜单数据
        ExcelUtils.createHiddenSheetData(hiddenSheet, menuItems, column);
    }


    /**
     * 创建隐藏sheet,并添加下拉框数据
     *
     * @param hiddenSheet
     * @param menuItems
     * @param column
     */
    public static void createHiddenSheetData(Sheet hiddenSheet, List<String> menuItems, int column) {
        //创建隐藏sheet,存储下拉菜单数据
        //隐藏sheet中添加菜单数据
        final int menuItemSize = menuItems.size();
        for (int i = 0; i < menuItemSize; i++) {
            Row row = hiddenSheet.createRow(i);
            //隐藏表的数据列必须和添加下拉菜单的列序号相同，否则不能显示下拉菜单
            row.createCell(column).setCellValue(menuItems.get(i));
        }
    }


    /**
     * 创建"名称"标签，用于链接
     *
     * @param workbook：excel
     * @param name：            名称
     * @param formulaText:公式文本
     */
    public static void crateNameTarget(Workbook workbook, String name, String formulaText) {
        //创建"名称"标签，用于链接
        Name namedCell = workbook.createName();
        namedCell.setNameName(name);
        namedCell.setRefersToFormula(formulaText);
    }

    /**
     * 添加下拉框序列选择数据
     *
     * @return
     */
    public static void addValidationData(XSSFSheet tarSheet, String formulaId, int firstRow, int lastRow, int firstCol, int lastCol) {

        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(tarSheet);
        DataValidation validation = ExcelUtils.getDataValidationByFormula(formulaId, firstRow, lastRow, firstCol, lastCol, dvHelper);
        tarSheet.addValidationData(validation);
    }


    /**
     * 创建单元格数据
     *
     * @param formulaString
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param dvHelper
     * @return
     */
    private static DataValidation getDataValidationByFormula(String formulaString, int firstRow, int lastRow, int firstCol, int lastCol, DataValidationHelper dvHelper) {

        //通过公式创建数据验证约束列表
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
        //创建单元格列表
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstCol, lastCol);
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);
        //设置此对象是否允许空值作为有效值
        dataValidation.setEmptyCellAllowed(false);

        if (dataValidation instanceof XSSFDataValidationHelper) {
            //设置输入无效值时的行为
            dataValidation.setShowErrorBox(true);
        } else {
            //如果列表将值显示到下拉列表中，则为True，
            //否则为false。换句话说，如果列表应该显示右边的箭头标志
//            dataValidation.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        dataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");

        return dataValidation;
    }


    /**
     * 生成多级excel级联下拉列表
     *
     * @param filePath   文件路径
     * @param mapOneList 一级数据
     * @param map        N级数据（需递归获取每层数据，统一封装为map）
     * @param startRow   开始插入行
     * @param startCol   开始插入列
     * @param levels     N级联动
     */
    public static void exportCascadeExcel(String filePath, List<String> mapOneList,
                                          Map<String, List<String>> map,
                                          Integer startRow, Integer startCol, Integer levels) {
        try {
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = ExcelUtils.addCascadeDropDownList(fis, mapOneList, map, startRow, startCol, levels);
            workbook.write(ExcelUtils.createFile(filePath));
        } catch (Exception e) {
            log.error("导出失败: " + e.getMessage());
        }
    }

    /**
     * 生成多级excel级联下拉列表
     *
     * @param filePath   文件路径
     * @param mapOneList 一级数据
     * @param map        N级数据（需递归获取每层数据，统一封装为map）
     * @param startRow   开始插入行
     * @param startCol   开始插入列
     * @param levels     N级联动
     */
    public static Workbook createCascadeExcel(String filePath, List<String> mapOneList, Map<String, List<String>> map,
                                              Integer startRow, Integer startCol, Integer levels) throws FileNotFoundException {
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = ExcelUtils.addCascadeDropDownList(fis, mapOneList, map, startRow, startCol, levels);
        return workbook;
    }


    /**
     * 添加级联下来列表
     *
     * @param is         文件路径
     * @param mapOneList 一级数据
     * @param map        N级数据（需递归获取每层数据，统一封装为map）
     * @param startRow   开始插入行
     * @param startCol   开始插入列
     * @param levels     N级联动
     */
    public static Workbook addCascadeDropDownList(InputStream is, List<String> mapOneList,
                                                  Map<String, List<String>> map, Integer startRow,
                                                  Integer startCol, Integer levels) {
        Workbook workbook = createWorkbook(is);
        ExcelUtils.addCascadeDropDownList(workbook, mapOneList, map, startRow, startCol, levels);
        return workbook;
    }

    /**
     * @param workbook   文件路径
     * @param mapOneList 一级数据
     * @param map        N级数据（需递归获取每层数据，统一封装为map）
     * @param startRow   开始插入行
     * @param startCol   开始插入列
     * @param levels     N级联动
     */
    public static Workbook addCascadeDropDownList(Workbook workbook, List<String> mapOneList, Map<String, List<String>> map,
                                                  Integer startRow, Integer startCol, Integer levels) {
        // 1、创建Excel
        Sheet mainSheet = workbook.getSheetAt(0);

        //2、N级联动 sheet
        Sheet mapSheet = workbook.createSheet("SHEET_LIST");
        //联动关系写入mapSheet,设置sheet是否隐藏
        workbook.setSheetHidden(workbook.getSheetIndex(mapSheet), true);

        // 3、将数据写入隐藏的sheet中并做好关联关系
        ExcelUtils.writeData(workbook, mapSheet, mapOneList, map);//

        // 4、设置数据有效性----CharStatusEnum.A从第几列开始插入，A从第1列，B第二列
        for (CharStatusEnum charStatusEnum : CharStatusEnum.values()) {
            if (charStatusEnum.getValue() == startCol) {
                ExcelUtils.setDataValid(workbook, mainSheet, mapOneList, charStatusEnum, startRow, levels);
            }
        }

        return workbook;
    }


    /**
     * 将数据写入隐藏的sheet中并做好关联关系
     *
     * @param workBook
     * @param mapSheet
     * @param parentList
     * @param siteMap
     */
    private static void writeData(Workbook workBook, Sheet mapSheet, List<String> parentList, Map<String, List<String>> siteMap) {

        //循环将父数据写入siteSheet的第1行中
        int siteRowId = 0;
        Row parentRow = mapSheet.createRow(siteRowId);
        parentRow.createCell(0).setCellValue("父列表");

        for (int i = 0; i < parentList.size(); i++) {
            //有多少个省，创建多少个下拉框
            parentRow.createCell(i + 1).setCellValue(parentList.get(i));
        }

        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = siteMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String parentName = keyIterator.next();
            List<String> sonList = siteMap.get(parentName);

            Row childRow = mapSheet.createRow(siteRowId++);
            childRow.createCell(0).setCellValue(parentName);
            final int sonSize = sonList.size();
            for (int i = 0; i < sonSize; i++) {
                childRow.createCell(i + 1).setCellValue(sonList.get(i));
            }

            // 添加名称管理器: 偏移量、第几行、一共多少列
            String range = ExcelUtils.getRange(1, siteRowId, sonSize);
            String formula = mapSheet.getSheetName() + "!" + range;

            //创建"名称"标签，用于链接
            ExcelUtils.crateNameTarget(workBook, parentName, formula);
        }
    }

    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }


    /**
     * 设置子级数据
     *
     * @param workbook
     * @param mainSheet
     * @param parentList
     * @param charEnum
     * @param startRow
     * @param levels
     */
    private static void setDataValid(Workbook workbook, Sheet mainSheet, List<String> parentList, CharStatusEnum charEnum, Integer startRow, Integer levels) {
        //设置省份下拉
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) mainSheet);
        List<String> dataList = Arrays.asList(parentList.toArray(new String[0]));

        String hiddenSheetName = "hidden";
        ExcelUtils.createHiddenSheetData(workbook, hiddenSheetName, dataList, 0);

        //创建"名称"标签，用于链接
        ExcelUtils.crateNameTarget(workbook, hiddenSheetName, hiddenSheetName + "!$A$1:$A$" + dataList.size());

        //加载数据,将名称为hidden的
        DataValidation dataValidation = ExcelUtils.getDataValidationByFormula(hiddenSheetName, startRow - 1, ENDROW, charEnum.getValue() - 1, charEnum.getValue() - 1, dvHelper);
        mainSheet.addValidationData(dataValidation);

        // 设置非第一层级的数据下拉
        for (int i = 0; i <= 10; i++) {
            // setDataValidation(mainSheet, 'B', i + 1, 2);// "B"是指父类所在的列，i+1初始值为1代表从第2行开始
            // 2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么对应的第四个参数就为3
            ExcelUtils.setDataValidation(mainSheet, charEnum.getCharacter(), i + 1, charEnum.getValue(), levels);
        }
    }


    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    private static void setDataValidation(Sheet sheet, char offset, int rowNum, int colNum, Integer levels) {
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        for (Integer i = 0; i < levels - 1; i++) {
            final String formulaString = "INDIRECT($" + (char) (offset + i) + (rowNum) + ")";
            final DataValidation dataValidation = getDataValidationByFormula(formulaString, rowNum - 1, rowNum - 1, colNum + i, colNum + i, dvHelper);
            sheet.addValidationData(dataValidation);
        }
    }


    /**
     * 创建输出文件
     *
     * @param filePath
     * @return
     */
    public static OutputStream createFile(String filePath) {
        OutputStream os = null;
        try {
            final int index = filePath.lastIndexOf("\\");
            String path = filePath.substring(0, index);
            File f = new File(path);
            if (!f.exists()) {
                f.mkdirs();
            }

            // 创建可写入的Excel工作簿
            File file = new File(filePath);
            if (!file.exists()) {
                boolean bool = file.createNewFile();
                log.info("文件不存在，重新生成成功：" + bool);
            } else {
                file.delete();
                file.createNewFile();
                log.info("文件已存在，删除后重新生成成功");
            }
            os = new FileOutputStream(filePath);
        } catch (Exception e) {
            log.info("生成文件失败：" + e.getMessage());
        }
        return os;
    }

    /**
     * 三级联动指定插入位置，eg: B为指定第二列插入，C为指定第三列插入
     */
    public enum CharStatusEnum {

        A('A', 1), B('B', 2), C('C', 3),
        D('D', 4), E('E', 5), F('F', 6),
        G('G', 7), H('H', 8), I('I', 9), J('J', 10);

        private char character;
        private int value;

        //构造方法
        CharStatusEnum(char character, int value) {
            this.character = character;
            this.value = value;
        }

        //自定义方法
        public void setCharacter(char character) {
            this.character = character;
        }

        public void setValue(int value) {
            this.value = value;
        }

        public char getCharacter() {
            return character;
        }

        public int getValue() {
            return value;
        }

        @Override
        public String toString() {
            return "CharStatusEnum{" +
                    "character=" + character +
                    ", value=" + value +
                    '}';
        }
    }


    /*********************************合并单元格***************************************************/
    /**
     * 获取第一个sheet
     *
     * @param file
     * @return
     */
    public static Sheet getSheet(MultipartFile file) {
        return getSheet(file, 0);
    }

    /**
     * 获取指定的sheet
     *
     * @param file
     * @return
     */
    public static Sheet getSheet(MultipartFile file, int sheetNum) {
        Sheet sheet = null;
        try {
            sheet = getSheet(file.getInputStream(), sheetNum);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sheet;
    }

    /**
     * 获取指定的sheet
     *
     * @param in
     * @return
     */
    public static Sheet getSheet(InputStream in, int sheetNum) {
        Sheet sheet = null;
        try {
//            Workbook workbook = createWorkbook(in);
            Workbook workbook = new XSSFWorkbook(in);
            sheet = workbook.getSheetAt(sheetNum);
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(in);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sheet;
    }

    /**
     * 获取excel数据
     *
     * @param sheet：            sheet
     * @param startRowNum:开始行编号
     * @param dataRowNum：       结束行编号
     * @param startColNum：开始列编号
     * @param endColNum：结束列编号
     * @return
     */
    public static Map<String, String> getExcelData(Sheet sheet, int startRowNum, int dataRowNum, int startColNum, int endColNum) {
        Map<String, String> map = new HashMap<>();
        final Row firstRow = sheet.getRow(startRowNum);
        final Row dataRow = sheet.getRow(dataRowNum);
        for (Cell cell : firstRow) {
            if (cell == null) {
                continue;
            }

            final int columnIndex = cell.getColumnIndex();
            if (columnIndex > startColNum && columnIndex < endColNum && dataRow != null) {
                //合并单元格
                ExcelUtils.mergerCell(sheet, cell);

                final String stringCellValue = cell.getStringCellValue();
                final Cell cell1 = dataRow.getCell(columnIndex);
                cell1.setCellType(CellType.STRING);
                final String stringCellValue2 = cell1.getStringCellValue();

                map.put(stringCellValue, stringCellValue2);
            }
        }
        return map;
    }

    /**
     * 获取excel指定列明的角标
     *
     * @param sheet
     * @param startRow
     * @param columnName
     * @return
     */
    public static int getColumnIndex(Sheet sheet, int startRow, String columnName) {
        int columnIndex = 0;
        final Row row = sheet.getRow(startRow);
        for (Cell cell : row) {
            ExcelUtils.mergerCell(sheet, cell);
            final String stringCellValue = cell.getStringCellValue();
            if (columnName.equals(stringCellValue)) {
                columnIndex = cell.getColumnIndex();
            }
        }
        return columnIndex;
    }

    /**
     * 获取excel导入sheet数据的列名称
     *
     * @param file
     * @return
     */
    public static List<String> getExcelColumn(MultipartFile file) {
        List<String> columns = new ArrayList<>();
        if (file == null) {
            return columns;
        }

        Sheet sheet = ExcelUtils.getSheet(file, 0);
        columns = getRowData(sheet, 0);
        return columns;
    }

    /**
     * 获取excel中sheet数据
     *
     * @param file
     * @return
     */
    public static List<String> getColumnIndex(MultipartFile file, int sheetNum, int rowNum) {
        List<String> columnList = new ArrayList<>();
        try {
            final Workbook workbook = WorkbookFactory.create(file.getInputStream());
            final Sheet sheet = workbook.getSheetAt(sheetNum);
            columnList = getRowData(sheet, rowNum);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return columnList;
    }


    /**
     * 获取excel列数据
     *
     * @param sheet
     * @param rowNum
     * @return
     */
    public static List<String> getRowData(Sheet sheet, int rowNum) {
        final Row row = sheet.getRow(rowNum);
        List<String> resList = getRowData(row);
        return resList;
    }

    /**
     * 获取excel 数据列名称（列名合并单元格的情况）
     *
     * @param sheet
     * @return
     */
    public static Map<String, Integer> getExcelColumnMap(Sheet sheet) {
        return getColumn(sheet.getMergedRegions());
    }

    /**
     * 获取excel 合并单元格列参数
     *
     * @param mergedRegions
     * @return
     */
    public static Map<String, Integer> getColumn(List<CellRangeAddress> mergedRegions) {
        Map<String, Integer> map = new HashMap<>();
        int columnIndex = 0;

        for (CellRangeAddress mergedRegion : mergedRegions) {
            if (columnIndex >= mergedRegion.getFirstColumn() && columnIndex <= mergedRegion.getLastColumn()) {
                //列名所在合并单元格的起始行
                int firstRow = mergedRegion.getFirstRow();
                //列名所在合并单元格的结束行
                int lastColumn = mergedRegion.getLastColumn();
                //合并单元格行数
                final int mergeRowsNum = lastColumn - firstRow + 1;

                map.put("firstRow", firstRow);
                map.put("lastColumn", lastColumn);
                map.put("mergeRowsNum", mergeRowsNum);
                break;
            }
        }
        return map;
    }


    /**
     * 获取合并单元格标题头信息
     *
     * @param file
     * @param sheetNum:    shee数量，从第几个sheet开始解析数据
     * @param startRowNum： 开始行，从第几行开始读取列数据
     * @param endRowNum    结束行，从第几行结束读取列数据，包含头部、尾部
     * @return
     */
    public static <T> List<String> getSheetHeader(MultipartFile file, int sheetNum, int startRowNum, int endRowNum) {
        final Sheet sheet = ExcelUtils.getSheet(file, sheetNum);
        List<String> list = ExcelUtils.getSheetHeader(sheet, startRowNum, endRowNum);
        return list;
    }

    /**
     * 获取合并单元格标题头信息
     *
     * @param sheet
     * @param startRowNum： 开始行，从第几行开始读取列数据
     * @param endRowNum    结束行，从第几行结束读取列数据，包含头部、尾部
     * @return
     */
    public static <T> List<String> getSheetHeader(Sheet sheet, int startRowNum, int endRowNum) {
        List<String> headerList = new ArrayList<>();
        for (Row row : sheet) {
            final int rowNum = row.getRowNum();
            if (rowNum < startRowNum || rowNum >= endRowNum) {
                continue;
            }

            for (Cell cell : row) {
                if (ExcelUtils.isMergedCell(sheet, cell)) {
                    //合并单元格
                    ExcelUtils.mergerCell(sheet, cell);
                    cell.setCellType(CellType.STRING);
                    final String stringCellValue = cell.getStringCellValue();
                    if (!headerList.contains(stringCellValue)) {
                        headerList.add(stringCellValue);
                    }
                } else {
                    cell.setCellType(CellType.STRING);
                    headerList.add(cell.getStringCellValue());
                }
            }
        }
        return headerList;
    }

    /**
     * @param sheet
     * @param lastRow：最后一行
     * @param clazz：       返回数据类型
     * @param <T>
     * @return
     */
    public static <T> List<T> getMergeCelleExcel(Sheet sheet, int lastRow, Class<T> clazz) {
        List<T> resList = new ArrayList<>();

        return resList;
    }


    /**
     * 合并单元格处理，只是获取单元格数据，不需要列名
     *
     * @param file
     * @param startRowNum
     * @return
     */
    public static <T> List<T> importMergeExcel(Sheet sheet, int startRowNum, Class<T> clazz) {
        List<T> resList = new ArrayList();
        try {
            return importMergeExcel(sheet, startRowNum, null, clazz);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resList;
    }


    /**
     * 合并单元格处理，只是获取单元格数据，不需要列名
     *
     * @param sheet
     * @param startRowNum
     * @return
     */
    public static <T> List<T> importMergeExcel(Sheet sheet, int startRowNum, String endOffset, Class<T> clazz) {
        List<T> resList = new ArrayList();
        try {
            Map<String, String> colIndexFeildMap = new HashMap<>();
            final Map<String, String> excelColDescs = ExcelUtils.getExcelColDescs(clazz);

            for (Row row : sheet) {
                final int rowNum = row.getRowNum();
                if (rowNum < startRowNum) {
                    continue;
                }

                if (rowNum == startRowNum) {
                    colIndexFeildMap = ExcelUtils.getColIndexFeild(row, excelColDescs, colIndexFeildMap);
                } else {
                    final T obj = clazz.newInstance();
                    boolean flag = true;
                    for (Cell cell : row) {
                        if (cell == null) {
                            continue;
                        }

                        ExcelUtils.mergerCell(sheet, cell);
                        cell.setCellType(CellType.STRING);
                        final String stringCellValue = cell.getStringCellValue();
                        if (StringUtils.isNotBlank(stringCellValue) && stringCellValue.equals(endOffset)) {
                            //解析到结束为止，说明已经读取完成
                        } else {
                            final int columnIndex = cell.getColumnIndex();
                            if (!colIndexFeildMap.containsKey(columnIndex + "")) {
                                continue;
                            }
                            final String feildName = colIndexFeildMap.get(columnIndex + "");
                            ReflectUtils.setFieldValue(obj, feildName, stringCellValue);
                            flag = true;
                        }
                    }

                    if (flag) {
                        resList.add(obj);
                    }
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return resList;
    }

    /**
     * 合并单元格处理，只是获取单元格数据，不需要列名
     *
     * @param file
     * @param startRowNum
     * @return
     */
    public static <T> List<T> importMergeCellExcel(MultipartFile file, int startRowNum, Class<T> clazz) {
        List<T> resList = new ArrayList();
        try {
            return importMergeCellExcel(file.getInputStream(), startRowNum, clazz);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resList;
    }

    /**
     * 合并单元格处理，只是获取单元格数据，不需要列名
     *
     * @param in
     * @param startRowNum
     * @return
     */
    public static <T> List<T> importMergeCellExcel(InputStream in, int startRowNum, Class<T> clazz) {
        return importMergeCellExcel(in, 0, startRowNum, clazz);
    }

    /**
     * 合并单元格处理，只是获取单元格数据，不需要列名
     *
     * @param in:                                     文件流
     * @param sheetNum：sheet编号，获取第几个sheet数据
     * @param startRowNum：开始行号，从第几行开始获取合并单元格的标题头（列名称）
     * @param clazz：返回对象
     * @param <T>
     * @return
     */
    public static <T> List<T> importMergeCellExcel(InputStream in, int sheetNum, int startRowNum, Class<T> clazz) {
        List<T> resList = new ArrayList();
        //获取第几个sheet数据
        final Sheet sheet = ExcelUtils.getSheet(in, sheetNum);
        if (sheet == null) {
            return resList;
        }

        Map<String, String> colIndexFieldMap = new HashMap<>();
        final Map<String, String> colDescMap = ExcelUtils.getExcelColDescs(clazz);
        for (Row row : sheet) {
            final int rowNum = row.getRowNum();
            if (rowNum >= startRowNum) {
                //判断单元格是否有合并单元格
                ExcelUtils.mergerCell(sheet, row);
            }

            if (rowNum == startRowNum) {
                colIndexFieldMap = ExcelUtils.getColIndexFeild(row, colDescMap, colIndexFieldMap);
            } else if (rowNum >= startRowNum) {
                final T obj = ExcelUtils.setColFieldValue(row, colIndexFieldMap, clazz);
                if (obj != null) {
                    resList.add(obj);
                }
            }
        }
        return resList;
    }


    /**
     * 获取合并单元格
     *
     * @param in
     * @param sheetNum：第几个sheet
     * @param startMergeRowNum：从第几合并单元格的行开始，将合并单元格数据填充到每一行
     * @param startColNum：从第列开始解析合并excel标题头数据
     * @param startDataRow：从第几行开始获取行数据
     * @return
     */
    public static <T> List<T> importMergeCellExcel(InputStream in, int sheetNum, int startMergeRowNum, int startColNum, int startDataRow, Class<T> clazz) {
        List<T> resList = new ArrayList();
        try {
            Map<String, String> colIndexFieldMap = new HashMap<>();
            Sheet sheet = ExcelUtils.getSheet(in, sheetNum);
            if (sheet == null) {
                return resList;
            }

            final Map<String, String> colDescMap = ExcelUtils.getExcelColDescs(clazz);
            for (Row row : sheet) {
                final int rowNum = row.getRowNum();
                if (rowNum >= startMergeRowNum) {
                    //列所在的行大于 sheet行，则表示有合并单元格
                    ExcelUtils.mergerCell(sheet, row);
                }

                if (rowNum == startColNum) {
                    //第一行,获取列名称
                    colIndexFieldMap = ExcelUtils.getColIndexFeild(row, colDescMap, colIndexFieldMap);
                } else if (rowNum >= startDataRow) {
                    //大于第一行，则为数据，根据列名称获取对应的数据
                    T obj = ExcelUtils.setColFieldValue(row, colIndexFieldMap, clazz);
                    resList.add(obj);
                }
            }
            System.out.println("excel解析结果： " + resList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resList;
    }

    /**
     * 合并单元格处理，只是获取单元格数据，不需要列名
     *
     * @param sheet
     * @param startRowNum
     * @return
     */
    public static List<String> getMergeCellRowData(Sheet sheet, int startRowNum) {
        List<String> resList = new ArrayList();
        for (Row row : sheet) {
            final int rowNum = row.getRowNum();
            if (rowNum >= startRowNum) {
                //判断单元格是否有合并单元格
                ExcelUtils.mergerCell(sheet, row);

                final List<String> rowData = ExcelUtils.getRowData(row);
                if (CollectionUtils.isNotEmpty(rowData)) {
                    resList.addAll(rowData);
                }
            }
        }
        return resList;
    }


    /**
     * 获取行数据，不包含列名
     *
     * @param row
     */
    public static List<String> getRowData(Row row) {
        List<String> resList = new ArrayList();
        final short firstCellNum = row.getFirstCellNum();
        final short lastCellNum = row.getLastCellNum();

        for (int i = firstCellNum; i < lastCellNum; i++) {
            final Cell cell = row.getCell(i);
            if (cell == null) {
                continue;
            }

            cell.setCellType(CellType.STRING);
            final String cellValue = cell.getStringCellValue();
            resList.add(cellValue);
        }
        return resList;
    }

    /**
     * 获取指定实体类 中标注 @Excel注解的列中文名及列英文名，存储方式为：列中文名称-列名
     */
    public static Map<String, String> getExcelColDescs(Class clazz) {
        Map<String, String> map = new HashMap<>();

        final Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            final Field field = fields[i];
            final Excel annotation = field.getAnnotation(Excel.class);
            if (annotation != null) {
                final String name = annotation.name();
                //列中文名称-列名
                map.put(name, field.getName());
            }
        }
        return map;
    }

    /**
     * 获取列角标位置的属性字段
     *
     * @param row:              行数据
     * @param colMap：列集合（列值-名称）
     * @param tempMap:          列角标-列名称
     * @return： 返回值为：key为列角标，值为列英文名称
     */
    public static Map<String, String> getColIndexFeild(Row row, Map<String, String> colMap, Map<String, String> tempMap) {
        final short firstCellNum = row.getFirstCellNum();
        final short lastCellNum = row.getLastCellNum();

        for (int i = firstCellNum; i < lastCellNum; i++) {
            final String cellValue = row.getCell(i).getStringCellValue();
            if (StringUtils.isNotBlank(cellValue) && colMap.containsKey(cellValue.trim())) {
                final String fieldName = colMap.get(cellValue.trim());
                //列角标-列名称
                tempMap.put(i + "", fieldName);
            }
        }
        return tempMap;
    }


    /**
     * 获取列角标位置的属性字段
     *
     * @param row:     行数据
     * @param tempMap: 列名标题（列角标-列名称）
     * @return 列集合（列英文名称-列值）
     */
    public static Map<String, String> getColFields(Row row, Map<String, String> tempMap) {
        Map<String, String> colMap = new HashMap<>();
        final short firstCellNum = row.getFirstCellNum();
        final short lastCellNum = row.getLastCellNum();

        for (int i = firstCellNum; i < lastCellNum; i++) {
            if (tempMap.containsKey(i + "")) {
                final String fieldName = tempMap.get(i + "");
                final Cell cell = row.getCell(i);
                if (cell == null) {
                    continue;
                }
                cell.setCellType(CellType.STRING);
                final String cellValue = cell.getStringCellValue();
                colMap.put(fieldName, cellValue);
            }
        }
        return colMap;
    }

    /**
     * 将单元格数据，列名 与实体对应
     *
     * @param row:     行数据
     * @param tempMap: 列名标题（列角标-列名称）
     * @return 列集合（列英文名称-列值）
     */
    public static <T> T setColFieldValue(Row row, Map<String, String> tempMap, Class<T> clazz) {
        T obj = null;
        try {
            obj = clazz.newInstance();
            final short firstCellNum = row.getFirstCellNum();
            final short lastCellNum = row.getLastCellNum();

            for (int colIndex = firstCellNum; colIndex < lastCellNum; colIndex++) {
                final String columnIndex = colIndex + "";
                if (tempMap.containsKey(columnIndex)) {
                    final Cell cell = row.getCell(colIndex);
                    if (cell == null) {
                        continue;
                    }

                    cell.setCellType(CellType.STRING);
                    final String cellValue = cell.getStringCellValue();//列对应的值
                    final String fieldName = tempMap.get(columnIndex);//列英文名称
                    ReflectUtils.setFieldValue(obj, fieldName, cellValue);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return obj;
    }

    /**
     * 判断sheet中的列是否合并单元格
     *
     * @param sheet
     * @param cell
     */
    public static boolean isMergedCell(Sheet sheet, Cell cell) {
        final List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            if (mergedRegion.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                return true;
            }
        }
        return false;
    }

    /**
     * 获取合并单元格列
     *
     * @param sheet
     * @param cell
     * @return
     */
    public static CellRangeAddress getMergedRegions(Sheet sheet, Cell cell) {
        final List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            if (mergedRegion.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                return mergedRegion;
            }
        }
        return null;
    }

    /**
     * 合并单元格,将合并单元格的值赋值
     *
     * @param sheet
     * @param cell
     */
    public static void mergerCell(Sheet sheet, Cell cell) {
        if (ExcelUtils.isMergedCell(sheet, cell)) {
            CellRangeAddress mergedRegions = ExcelUtils.getMergedRegions(sheet, cell);
            final int firstRow = mergedRegions.getFirstRow();
            final int lastRow = mergedRegions.getLastRow();
            final int firstColumn = mergedRegions.getFirstColumn();
            final int lastColumn = mergedRegions.getLastColumn();

            final Cell topCell = sheet.getRow(firstRow).getCell(firstColumn);
            final String cellValue = topCell.getStringCellValue();
            cell.setCellValue(cellValue);
        }
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param row
     */
    public static void mergerCell(Sheet sheet, Row row) {
        for (Cell cell : row) {
            ExcelUtils.mergerCell(sheet, cell);
        }
    }


    /**
     * 导出压缩文件
     *
     * @param in:               文件流
     * @param fileName：文件名，自定义
     * @param outFileName：压缩文件名，全路径
     */
    public static void exportZip(InputStream in, String fileName, String outFileName) throws IOException {

        Workbook workbook = createWorkbook(in);
        ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
        // 将工作簿写入字节输出流
        workbook.write(byteOut);
        byte[] workbookBytes = byteOut.toByteArray();

        // 使用ZipOutputStream将字节输出流压缩为.xlsx文件
        ZipOutputStream out = new ZipOutputStream(new FileOutputStream(outFileName));
        ZipEntry zipEntry = new ZipEntry(fileName);
        out.putNextEntry(zipEntry);
        out.write(workbookBytes);
        out.closeEntry();

        // 关闭流
        out.close();
        byteOut.close();
        workbook.close();
    }

    public static void main(String[] args) {

        try {
            InputStream in = new FileInputStream("d://bill.xlsx");
            String zipFileName = "d://export.zip";
            String fileName = "sheet1.xlsx";
            exportZip(in, fileName, zipFileName);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

}
